/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package model.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.dao.services.UserDAOServices;
import model.entities.Role;
import model.entities.User;
import java.lang.StackOverflowError;

/**
 *
 * @author Ku81
 */
public class UserDAO implements UserDAOServices {

    private static UserDAO userDao;

    public static UserDAO getInstance() {
        if (userDao == null) {
            userDao = new UserDAO();
        }
        return userDao;
    }

    @Override
    public List<User> getAll() {
        List<User> userList = new ArrayList<User>();
        try {
            Connection con = ConnectionFactory.getConnection();
            String sql = "SELECT * FROM tblUser";
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                User u = new User();
                u.setIdUser(rs.getInt("IdUser"));
                u.setUserName(rs.getString("userName"));
                u.setPassword(rs.getString("password"));
                u.setFullName(rs.getNString("fullName"));
                u.setDateOfBirth(rs.getDate("dateOfBirth"));
                u.setGender(rs.getBoolean("gender"));
                u.setHomeTown(rs.getNString("homeTown"));
                u.setSchool(rs.getNString("school"));
                u.setPhoneNumber(rs.getString("phoneNumber"));
                u.setEmail(rs.getString("email"));
                u.setStrength(rs.getNString("strength"));
                u.setLanguage(rs.getNString("language"));
                u.setImage(rs.getNString("image"));
                u.setAbout(rs.getNString("about"));
                u.setFarvoriteQuotations(rs.getNString("favoriteQuotations"));
                u.setFacebookLink(rs.getNString("facebookLink"));
                u.setActiveCode(rs.getNString("activeCode"));
                Role role = RoleDAO.getInstance().getById(rs.getInt("idRole"));
                u.setRole(role);
                u.setIsActived(rs.getBoolean("isActived"));

//                List<News> newsList = NewsDAO.getInstance().
//                u.setProjectList(null);
//                List<Project> projectList = ProjectDAO.getInstance();
//                u.setNewsList(null);
                userList.add(u);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return userList;
    }

    @Override
    public User getByUserId(int idUser) {
        User u = null;
        try {
            Connection con = ConnectionFactory.getConnection();
            String sql = "SELECT * FROM tblUser WHERE idUser=?";
            PreparedStatement ps;

            ps = con.prepareStatement(sql);
            ps.setInt(1, idUser);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                u = new User();
                u.setIdUser(rs.getInt("IdUser"));
                u.setUserName(rs.getString("userName"));
                u.setPassword(rs.getString("password"));
                u.setFullName(rs.getNString("fullName"));
                u.setDateOfBirth(rs.getDate("dateOfBirth"));
                u.setGender(rs.getBoolean("gender"));
                u.setHomeTown(rs.getNString("homeTown"));
                u.setSchool(rs.getNString("school"));
                u.setPhoneNumber(rs.getString("phoneNumber"));
                u.setEmail(rs.getString("email"));
                u.setStrength(rs.getNString("strength"));
                u.setLanguage(rs.getNString("language"));
                u.setImage(rs.getNString("image"));
                u.setAbout(rs.getNString("about"));
                u.setFarvoriteQuotations(rs.getNString("favoriteQuotations"));
                u.setFacebookLink(rs.getNString("facebookLink"));
                u.setActiveCode(rs.getNString("activeCode"));
                Role role = RoleDAO.getInstance().getById(rs.getInt("idRole"));
                u.setRole(role);
                u.setIsActived(rs.getBoolean("isActived"));

            }

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return u;
    }

    @Override
    public User getByUserName(String userName) {
        User u = null;
        try {
            Connection con = ConnectionFactory.getConnection();
            String sql = "SELECT * FROM tblUser WHERE userName=?";
            PreparedStatement ps;

            ps = con.prepareStatement(sql);
            ps.setString(1, userName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                u = new User();
                u.setIdUser(rs.getInt("IdUser"));
                u.setUserName(rs.getString("userName"));
                u.setPassword(rs.getString("password"));
                u.setFullName(rs.getNString("fullName"));
                u.setDateOfBirth(rs.getDate("dateOfBirth"));
                u.setGender(rs.getBoolean("gender"));
                u.setHomeTown(rs.getNString("homeTown"));
                u.setSchool(rs.getNString("school"));
                u.setPhoneNumber(rs.getString("phoneNumber"));
                u.setEmail(rs.getString("email"));
                u.setStrength(rs.getNString("strength"));
                u.setLanguage(rs.getNString("language"));
                u.setImage(rs.getNString("image"));
                u.setAbout(rs.getNString("about"));
                u.setFarvoriteQuotations(rs.getNString("favoriteQuotations"));
                u.setFacebookLink(rs.getNString("facebookLink"));
                u.setActiveCode(rs.getNString("activeCode"));
                Role role = RoleDAO.getInstance().getById(rs.getInt("idRole"));
                u.setRole(role);
                u.setIsActived(rs.getBoolean("isActived"));

            }

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return u;
    }

    @Override
    public List<User> getByFullName(String fullName) {
        List<User> userList = new ArrayList<User>();
        try {
            Connection con = ConnectionFactory.getConnection();
            String sql = "SELECT * FROM tblUser WHERE fullName=?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, fullName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                User u = new User();
                u.setIdUser(rs.getInt("IdUser"));
                u.setUserName(rs.getString("userName"));
                u.setPassword(rs.getString("password"));
                u.setFullName(rs.getNString("fullName"));
                u.setDateOfBirth(rs.getDate("dateOfBirth"));
                u.setGender(rs.getBoolean("gender"));
                u.setHomeTown(rs.getNString("homeTown"));
                u.setSchool(rs.getNString("school"));
                u.setPhoneNumber(rs.getString("phoneNumber"));
                u.setEmail(rs.getString("email"));
                u.setStrength(rs.getNString("strength"));
                u.setLanguage(rs.getNString("language"));
                u.setImage(rs.getNString("image"));
                u.setAbout(rs.getNString("about"));
                u.setFarvoriteQuotations(rs.getNString("favoriteQuotations"));
                u.setFacebookLink(rs.getNString("facebookLink"));
                u.setActiveCode(rs.getNString("activeCode"));
                Role role = RoleDAO.getInstance().getById(rs.getInt("idRole"));
                u.setRole(role);
                u.setIsActived(rs.getBoolean("isActived"));

//                List<News> newsList = NewsDAO.getInstance().
//                u.setProjectList(null);
//                List<Project> projectList = ProjectDAO.getInstance();
//                u.setNewsList(null);
                userList.add(u);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return userList;
    }

    @Override
    public List<User> getByRoleId(int idRole) {
        List<User> userList = new ArrayList<>();

        try {
            Connection con = ConnectionFactory.getConnection();
            String sql = "SELECT * FROM tblUser WHERE idRole=?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, idRole);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                User u = new User();
                u.setIdUser(rs.getInt("idUser"));
                u.setUserName(rs.getString("userName"));
                u.setPassword(rs.getString("password"));
                u.setFullName(rs.getNString("fullName"));
                u.setDateOfBirth(rs.getDate("dateOfBirth"));
                u.setGender(rs.getBoolean("gender"));
                u.setHomeTown(rs.getNString("homeTown"));
                u.setSchool(rs.getNString("school"));
                u.setPhoneNumber(rs.getString("phoneNumber"));
                u.setEmail(rs.getString("email"));
                u.setStrength(rs.getNString("strength"));
                u.setLanguage(rs.getNString("language"));
                u.setImage(rs.getNString("image"));
                u.setAbout(rs.getNString("about"));
                u.setFarvoriteQuotations(rs.getNString("favoriteQuotations"));
                u.setFacebookLink(rs.getNString("facebookLink"));
                u.setActiveCode(rs.getNString("activeCode"));
                u.setIsActived(rs.getBoolean("isActived"));

//                List<News> newsList = NewsDAO.getInstance().
//                u.setProjectList(null);
//                List<Project> projectList = ProjectDAO.getInstance();
//                u.setNewsList(null);
                userList.add(u);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return userList;
    }

    @Override
    public boolean create(User user) {
        try {
            Connection con = ConnectionFactory.getConnection();
            String sql = "INSERT INTO tblUser VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getFullName());
            ps.setDate(4, (Date) user.getDateOfBirth());
            ps.setBoolean(5, user.isGender());
            ps.setString(6, user.getHomeTown());
            ps.setString(7, user.getSchool());
            ps.setString(8, user.getPhoneNumber());
            ps.setString(9, user.getEmail());
            ps.setString(10, user.getStrength());
            ps.setString(11, user.getLanguage());
            ps.setString(12, user.getImage());
            ps.setString(13, user.getAbout());
            ps.setString(14, user.getFarvoriteQuotations());
            ps.setString(15, user.getFacebookLink());
            ps.setString(16, user.getActiveCode());
            ps.setInt(17, user.getRole().getIdRole());
            ps.setBoolean(18, user.isIsActived());
            return ps.executeUpdate() == 1;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    @Override
    public boolean updateProfile(User user) {
        try {
            Connection con = ConnectionFactory.getConnection();
            String sql = "UPDATE tblUser SET password=?, fullName=?, dateOfBirth=?,"
                    + " gender=?, homeTown=?, school=?,"
                    + "phoneNumber=?, email=?, strength=?, [language]=?, [image]=?,"
                    + "about=?, favoriteQuotations=?, facebookLink=?,"
                    + " activeCode=?, idRole=?, isActived=?"
                    + "WHERE idUser=?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, user.getPassword());
            ps.setString(2, user.getFullName());
            ps.setDate(3, (Date) user.getDateOfBirth());
            ps.setBoolean(4, user.isGender());
            ps.setNString(5, user.getHomeTown());
            ps.setNString(6, user.getSchool());
            ps.setString(7, user.getPhoneNumber());
            ps.setString(8, user.getEmail());
            ps.setNString(9, user.getStrength());
            ps.setNString(10, user.getLanguage());
            ps.setNString(11, user.getImage());
            ps.setNString(12, user.getAbout());
            ps.setNString(13, user.getFarvoriteQuotations());
            ps.setNString(14, user.getFacebookLink());
            ps.setNString(15, user.getActiveCode());
            ps.setInt(16, user.getRole().getIdRole());
            ps.setBoolean(17, user.isIsActived());
            ps.setInt(18, user.getIdUser());

            return ps.executeUpdate() == 1;

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    @Override
    public boolean delete(int idUser) {
        try {
            Connection con = ConnectionFactory.getConnection();
            String sql = "DELETE tblUser WHERE idUser=?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, idUser);
            return ps.executeUpdate() == 1;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    @Override
    public boolean isExistUserName(String userName) {
        try {
            Connection con = ConnectionFactory.getConnection();
            String sql = "SELECT * FROM tblUser WHERE userName=?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, userName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                return true;
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    @Override
    public boolean checkLogin(String userName, String password) {
        try {
            Connection con = ConnectionFactory.getConnection();
            String sql = "SELECT * FROM tblUser WHERE userName=? AND [password]=?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, userName);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                return true;
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    @Override
    public List<User> find(String columnName, String value) {
        List<User> userList = new ArrayList<User>();
        try {
            Connection con = ConnectionFactory.getConnection();
            String sql = "SELECT * FROM ? WHERE userName LIKE '%?%'";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, columnName);
            ps.setNString(2, value);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                User u = new User();
                u.setIdUser(rs.getInt("IdUser"));
                u.setUserName(rs.getString("userName"));
                u.setPassword(rs.getString("password"));
                u.setFullName(rs.getNString("fullName"));
                u.setDateOfBirth(rs.getDate("dateOfBirth"));
                u.setGender(rs.getBoolean("gender"));
                u.setHomeTown(rs.getNString("homeTown"));
                u.setSchool(rs.getNString("school"));
                u.setPhoneNumber(rs.getString("phoneNumber"));
                u.setEmail(rs.getString("email"));
                u.setStrength(rs.getNString("strength"));
                u.setLanguage(rs.getNString("language"));
                u.setImage(rs.getNString("image"));
                u.setAbout(rs.getNString("about"));
                u.setFarvoriteQuotations(rs.getNString("favoriteQuotations"));
                u.setFacebookLink(rs.getNString("facebookLink"));
                u.setActiveCode(rs.getNString("activeCode"));
                Role role = RoleDAO.getInstance().getById(rs.getInt("idRole"));
                u.setRole(role);
                u.setIsActived(rs.getBoolean("isActived"));
//                List<News> newsList = NewsDAO.getInstance().
//                u.setProjectList(null);
//                List<Project> projectList = ProjectDAO.getInstance();
//                u.setNewsList(null);
                userList.add(u);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return userList;
    }

    @Override
    public boolean enable(int idUser, boolean values) {
        try {
            Connection con = ConnectionFactory.getConnection();
            String sql = "UPDATE tblUser SET isActived=? WHERE idUser=?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, idUser);
            ps.setBoolean(2, values);
            return ps.executeUpdate() == 1;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
}
